#coding=UTF-8
#This Python file uses the following encoding: utf-8

import openpyxl
import datetime
import time

StudentInfoDict = {
    'name': 'B',
    'exam-date': 'E',
    'exam-type': 'F',
    'exam-level': 'G'
}

class Student:
    def __init__(self, id:int, info:dict):
        self.id = id
        self.name = info['name']
        self.examType = info['exam-type']
        self.examLevel = info['exam-level']
        self.examDate = datetime.datetime.strptime(info['exam-date'],'%Y-%m-%d')

    def toString(self):
        return (
            "姓    名：" + self.name,
            "报考专业：" + self.examType,
            "报考等级：" + self.examLevel,
            "考试时间：%d年 %02d月 %02d日"%(self.examDate.year, self.examDate.month, self.examDate.day)
        ) 

class FileResolve:
    def __init__(self, xlsfile):
        self.xlsFile = openpyxl.load_workbook(xlsfile)
        self.studentFile = None
        self._progress = 0
        self.sheet1 = self.xlsFile.worksheets[0]
        self.students = list()

    def loadStudentExcel(self, filename):
        self.studentFile = openpyxl.load_workbook(filename)
        sheet = self.studentFile.worksheets[0]
        index = 2
        # 清数据
        self.students = list()
        while sheet['A%d'%index].value != None:
            sdnt = Student(index-1, {
                'name': sheet[StudentInfoDict['name']+str(index)].value,
                'exam-date': sheet[StudentInfoDict['exam-date']+str(index)].value,
                'exam-type': sheet[StudentInfoDict['exam-type']+str(index)].value,
                'exam-level': sheet[StudentInfoDict['exam-level']+str(index)].value,
            })
            self.addStudent(sdnt)
            index += 1
        print('录入学生信息%d条'%(index-2))
        return index-2

    def addStudent(self, sdnt:Student):
        self.students.append(sdnt)

    def fillExcel(self, sdnt:Student):
        # 左边一列的id是奇数，id从1开始
        column = 'A' if (sdnt.id % 2 == 1) else 'E'
        row_offset = ((sdnt.id-1)//2) * 18
        line = {
            'name': 5 + row_offset,
            'exam-type': 6 + row_offset,
            'exam-level': 7 + row_offset,
            'exam-date': 16 + row_offset
        }
        self.sheet1[column + str(line['name'])] = " 姓    名：" + sdnt.name
        self.sheet1[column + str(line['exam-type'])] = " 报考专业：" + sdnt.examType
        self.sheet1[column + str(line['exam-level'])] = " 报考等级：" + sdnt.examLevel
        self.sheet1[column + str(line['exam-date'])] = "考试时间：%d年 %02d月 %02d日"%(sdnt.examDate.year, sdnt.examDate.month, sdnt.examDate.day) 

        print(sdnt.toString())
    
    def generateTicket(self):
        if len(self.students) > 0:
            for sdnt in self.students:
                self.fillExcel(sdnt)
            self.saveFile()
        else:
            print("没有可用的学生信息")

    def saveFile(self):
        self.xlsFile.save(u'python-'+str(time.strftime("%Y%m%d", time.localtime()))+ u'.xlsx')

f = FileResolve('template.xlsx')
if f.loadStudentExcel('student-info.xlsx') <= 100:
    f.generateTicket()
    f.saveFile()
else:
    print('单次录入不得超过100名学生，请调整表格')